﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using bdxyit.com.RecruitmentAppSys.DBUtility;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：TitlesListModels
*系统时间：3/25/2012 3:38:08 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012年5月9日16:25:17
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class TitlesListDAL : ITitlesListDAL
    {
        private const string SQL_SELECT_TITLESLIST = " select TitlesID,TitlesName from TitlesLists ";

        private const string SQL_SELECT_TITLESLIST_BY_TitlesID = " select  top 1 TitlesID,TitlesName from TitlesLists "
                                                               + " where TitlesID=@TitlesID";

        private const string SQL_DELETE_TITLESLIST_BY_TitlesID = " delete from TitlesLists "
                                                               + " where TitlesID=@TitlesID";

        private const string SQL_UPDATE_TITLESLIST_BY_TitlesID = " update TitlesLists set "
                                                                + " TitlesID=@TitlesID,"
                                                                + " TitlesName=@TitlesName"
                                                                + " where TitlesID=@TitlesID";

        private const string SQL_INSERT_TITLESLIST = " insert into TitlesLists("
                                                    + " TitlesID,TitlesName)"
                                                    + "  values ("
                                                    + " @TitlesID,@TitlesName)";
        private const string FIELD_TitlesID = "@TitlesID";
        private const string FIELD_TitlesName = "@TitlesName";


        /// <summary>
        /// 查询所有记录
        /// </summary>
        /// <returns></returns>
        public List<TitlesList> SelectTitlesList()
        {
            List<TitlesList> ul = new List<TitlesList>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_TITLESLIST))
            {
                while (dr.Read())
                {
                    TitlesList u = new TitlesList();
                    u.TitlesID = dr["TitlesID"].ToString();
                    u.TitlesName = dr["TitlesName"].ToString();
                    ul.Add(u);
                }
            }
            return ul;
        }

        /// <summary>
        /// 查询一条记录根据TitlesID
        /// </summary>
        /// <param name="TitlesID"></param>
        /// <returns></returns>
        public TitlesList SelectTitlesListByTitlesID(string TitlesID)
        {
            TitlesList tl = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_TitlesID, SqlDbType.VarChar, 10);
            parm.Value = TitlesID;

            //执行读取家庭成员
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_TITLESLIST_BY_TitlesID, parm))
            {
                if (rdr.Read())
                {
                    tl = new TitlesList();
                    if (rdr.HasRows == true)
                    {
                        if (rdr["TitlesID"] != null && rdr["TitlesID"].ToString() != "")
                        {
                            tl.TitlesID = rdr["TitlesID"].ToString();
                        }
                        if (rdr["TitlesName"] != null && rdr["TitlesName"].ToString() != "")
                        {
                            tl.TitlesName = rdr["TitlesName"].ToString();
                        }
                    }
                }
            }
            return tl;
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="UniversityID"></param>
        /// <returns></returns>
        public bool DeleteTitlesListBYUniversityID(string TitlesID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_TitlesID, SqlDbType.VarChar, 10);
            parm.Value = TitlesID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_TITLESLIST_BY_TitlesID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 插入一条记录
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool InsertTitlesList(TitlesList tl)
        {
            bool flag = false;
            SqlParameter[] TitlesListParms = GetTitlesListParameters();
            SqlCommand cmd = new SqlCommand();
            TitlesListParms[0].Value = tl.TitlesID;
            TitlesListParms[1].Value = tl.TitlesName;

            foreach (SqlParameter parm in TitlesListParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_TITLESLIST;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }


        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="uni"></param>
        /// <returns></returns>
        public bool UpdateTitlesListBYUniversityID(TitlesList tl)
        {
            bool flag = false;
            SqlParameter[] TitlesListParms = GetTitlesListParameters();
            SqlCommand cmd = new SqlCommand();
            TitlesListParms[0].Value = tl.TitlesID;
            TitlesListParms[1].Value = tl.TitlesName;


            foreach (SqlParameter parm in TitlesListParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_TITLESLIST_BY_TitlesID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        private SqlParameter[] GetTitlesListParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
	                new SqlParameter(FIELD_TitlesID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_TitlesName, SqlDbType.VarChar,50)};
            return parms;
        }

    }
}